package common.main.cnkiOverseasPatent;

import common.main.cnkiPatent.CnkiPatentConfig;
import common.main.cnkiPatent.CnkiPatentData;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CnkiOverseasPatentSaveDataToSql {

    private static Connection conn = null;
    private static Statement stmt = null;

    private static void connectDatabase() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String databaseHost = CnkiPatentConfig.mysqlHost;//
        String databaseName = CnkiPatentConfig.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = CnkiPatentConfig.mysqlUserName;// MySQL默认的root账户名
        String password = CnkiPatentConfig.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"
                +databaseName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8";
        if(conn == null || conn.isClosed()){
            conn = DriverManager.getConnection(url);
        }
        if(stmt == null || stmt.isClosed()){
            stmt = conn.createStatement();
        }
    }

    public static List<String> getAllSearchKeywords(){
        List<String> keywordsList = new ArrayList();
        try{
            connectDatabase();
            String sql = "SELECT enKeyword,enShort FROM `datacrawl_searchkeyword` WHERE typeId LIKE '2;%' AND direction = '技术';";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                keywordsList.add(rs.getString("enKeyword"));
                keywordsList.add(rs.getString("enShort"));
            }
            for(int j = keywordsList.size() - 1;j >= 0;j--){
                if(keywordsList.get(j) == null){
                    keywordsList.remove(j);
                }
            }
        }catch ( Exception e){
            e.printStackTrace();
        }
        return keywordsList;
    }

    public static String getAllPublicNum(){
        try {
            connectDatabase();
            String sql = "select PublicNo from datasource_patent";

            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()){
                sb.append(rs.getString("PublicNo") + ",");
            }
            return sb.toString();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static void saveCnkiPaperToSql(List<CnkiPatentData> patentList) {
        try{
            connectDatabase();
            String sql_model = "INSERT INTO datasource_patent (Name,RequestNo,RequestDate,PublicNo,PublicDate,Proposer,Originator," +
                    "Brief,MainClassificationNo,Language,SearchKeyword,PatentUrl,Source)" +
                    "values ('<patentName>','<requestNum>','<requestDate>','<publicNum>','<publicDate>','<proposer>','<originator>'," +
                    "'<brief>','<mainClassificationNum>','en','<searchKeyword>','<patentUrl>','cnki')";
            for(int i = 0;i < patentList.size();i++){
                CnkiPatentData patent = patentList.get(i);
                patent.setPatentName(patent.getPatentName().replace("'","\\'").trim());
                patent.setProposer(patent.getProposer().replace("'","\\'").trim());
                patent.setBrief(patent.getBrief().replace("'","\\'").trim());
                patent.setOriginator(patent.getOriginator().replace("'","\\'").trim());
                patent.setSearchKeyword(patent.getSearchKeyword().replace("'","\\'").trim());
                patent.setPatentUrl(patent.getPatentUrl().replace("'","\\'").trim());

                String sql = sql_model.replace("<patentName>",patent.getPatentName())
                        .replace("<requestNum>",patent.getRequestNum())
                        .replace("<requestDate>",patent.getRequestDate())
                        .replace("<publicNum>",patent.getPublicNum())
                        .replace("<publicDate>",patent.getPublicDate())
                        .replace("<proposer>",patent.getProposer() + "")
                        .replace("<originator>",patent.getOriginator())
                        .replace("<brief>",patent.getBrief() + "")
                        .replace("<mainClassificationNum>",patent.getMainClassificationNum())
                        .replace("<searchKeyword>",patent.getSearchKeyword())
                        .replace("<patentUrl>",patent.getPatentUrl());

                try{
                    stmt.executeUpdate(sql);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            conn.close();
            stmt.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
